package sample;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author caiwl
 * @date 2020/6/12 10:42
 */
public class DBUtils {
    private static String url = "jdbc:mysql://127.0.0.1:3306";
    private static String user = "root";
    private static String password = "mysql";

    private static Connection conn = null;
    private static Statement st = null;
    private static ResultSet rs = null;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static List<String> getSchema() {
        List<String> list = new ArrayList<>();
        try {
            rs = getResultSet("select SCHEMA_NAME from information_schema.SCHEMATA order by SCHEMA_NAME");
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, st, rs);
        }
        return list;
    }

    public static List<String> getTable(String schema) {
        List<String> list = new ArrayList<>();
        try {
            rs = getResultSet("select TABLE_NAME from information_schema.`TABLES` where TABLE_SCHEMA='" + schema + "' order by TABLE_NAME");
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, st, rs);
        }
        return list;
    }

    public static List<String> getColumn(String schema, String table) {
        List<String> list = new ArrayList<>();
        try {
            rs = getResultSet("select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA='" + schema + "' and TABLE_NAME='" + table + "' order by ORDINAL_POSITION");
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, st, rs);
        }
        return list;
    }

    public static List<Map<String, Object>> getResult(String schema, String table, List<String> columns) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            rs = getResultSet("select * from " + schema + "." + table);
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>(columns.size());
                for (String columnLabel : columns) {
                    row.put(columnLabel, rs.getObject(columnLabel));
                }
                list.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, st, rs);
        }
        return list;
    }

    private static ResultSet getResultSet(String sql) throws Exception {
        conn = DriverManager.getConnection(url, user, password);
        st = conn.createStatement();
        st.execute(sql);
        System.out.println("execute sql: " + sql);
        return st.getResultSet();
    }

    private static void close(Connection conn, Statement st, ResultSet rs) {
        try {
            rs.close();
        } catch (SQLException e) {
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
            } finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
